package app.beit.fintrack.dao;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.SQLQuery;
import org.hibernate.Session;

import app.beit.fintrack.entity.summary.SummaryRepData;
import app.beit.fintrack.utils.DateFormatUtils;
import app.beit.fintrack.utils.enums.ESearchByStatus;

public class SummaryRepDao extends BaseDao {
	
	private final Logger log = Logger.getLogger(this.getClass());

	@SuppressWarnings("unchecked")
	public List<SummaryRepData> getDetailsByHospital(final String hospitalCode, final Date fromDate,
			final Date toDate, final int start, final int end, final String authType)throws Exception {
		this.log.info("get details data from hospital code : " + hospitalCode);
		final StringBuilder sb = new StringBuilder();
		sb.append("select sss.*, to_number(nvl(is_number(m.sums_serviceitem), 0)) as oldDischarge, ");
		sb.append("case when (m.code_id is null or m.code_id is not null) and (m.status in ('9', '4')) then  ");
		sb.append("nvl((select sum(act_amt) from m_sum_subfund where tran_id=sss.parentTranId AND (CANCEL_PAYMENT IS NULL OR CANCEL_PAYMENT = 'N')),0) ");
		sb.append("   else 0 ");
		sb.append("end as oldCompensation, ");
		sb.append("m.code_dw_hc as oldCodeDWHC, m.code_dw_inst as oldCodeDWINST, m.code_dw_ae as oldCodeDWAE, m.code_dw_ip as oldCodeDWIP, ");
		sb.append("m.code_dw_dmis as oldCodeDWDMIS, m.code_dw_ip_cslg as oldCodeDWIPCSLG, m.code_dw_op_cslg as oldCodeDWOPCSLG, ");
		sb.append("m.code_dw_pa_cslg as oldCodeDWPACSLG, m.code_dw_inst_cslg as oldCodeDWINSTCSLG, m.code_dw_ot_cslg as oldCodeDWOTCSLG,");
		sb.append("case when m.code_id is null and m.code_dw_hc='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPHC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'OPHC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as oldSumsHC, ");
		sb.append("case when m.code_id is null and m.code_dw_ae='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'OPAE','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPNB','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPPUC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IP3SSS','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IP7SSS','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CARAE','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CARREF','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CARREF-PUC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as oldSumsAE, ");
		sb.append("case when m.code_id is null and m.code_dw_inst='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'OPINST','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'INST','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as oldSumsINST, ");
		sb.append("case when m.code_id is null and m.code_dw_ip='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPAEC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPAER','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPINRGC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPINRGR','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPINSPSN','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPPRCC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPPRCC-PUC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as oldSumsIP, ");
		sb.append("case when m.code_id is null and m.code_dw_dmis='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CATARACT','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CATINST','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'DMISRC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_EXPENSE_AMT_KIDNEY(TRAN_ID,'DMISRC')), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'RCUHOSC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_EXPENSE_AMT_KIDNEY(TRAN_ID,'RCUHOSC')), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'RCUHOSR','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_EXPENSE_AMT_KIDNEY(TRAN_ID,'RCUHOSR')), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LLOP','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LLRGC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LLRGR','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LP','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'STROKE-DRUG','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'DMIDML','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'FPNHSO','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as oldSumsDMIS, ");
		sb.append("to_number(nvl(is_number(FN_SUMS_SUBFUND(m.TRAN_ID,'DRUG','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) as oldSumsDRUG, ");
		/**
		 * --*** CS,LG *** --
		 */
		sb.append("case when m.code_id is null and m.code_dw_ip_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'IP')), 0)) ");
		sb.append(" else null end as oldSumsIPCS, ");
		sb.append("case when m.code_id is null and m.code_dw_op_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'OP')), 0)) ");
		sb.append(" else null end as oldSumsOPCS,");
		sb.append("case when m.code_id is null and m.code_dw_pa_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'PA')), 0)) ");
		sb.append(" else null end as oldSumsPACS,");
		sb.append("case when m.code_id is null and m.code_dw_inst_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'INST')), 0))");
		sb.append(" else null end as oldSumsINSTCS, ");
		sb.append("case when m.code_id is null and m.code_dw_ot_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'OT')), 0)) ");
		sb.append(" else null end as oldSumsOTCS,");
		sb.append("is_number(FN_SUMS_SUBFUND_CS(m.tran_id,'DRUG'))as oldSumsDRUGCS ");
		sb.append(" from ");
		sb.append("(select rownum as id, M.TRAN_ID as tranId, m.parent_tran_id as parentTranId, a.hosp_rep_id as repNo, ");
		sb.append("m.eclaim_no as eclaimno, m.pid as pid, m.hn as hn, m.an as an, concat(concat(m.fname, ' '), m.lname) as fullname, ");
		sb.append("to_date(m.dateadm, 'yyyyMMdd') as admiddate, m.timeadm as admidtime, to_date(m.datedsc, 'yyyyMMdd') as dischargedate, m.timedsc as dischargetime, ");
		sb.append("case when m.person_type is not null then ");
		sb.append("   case when m.person_type='1' then 'OP' ");
		sb.append("        when m.person_type='2' then 'IP' end ");
		sb.append(" else ");
		sb.append("   case when (m.hn is not null and m.hn<>'-') and (m.an is null or m.an<>'-') then 'OP' ");
		sb.append("        when (m.hn is null or m.hn<>'-') and (m.an is not null and m.an<>'-') then 'IP' ");
		sb.append("		   when (m.hn is not null and m.hn<>'-') and (m.an is not null and m.an<>'-') then 'IP' end ");
		sb.append("end as personType, ");
		sb.append("to_number(replace(m.sums_serviceitem, ',', '')) as discharge, ");
		sb.append("case when (m.code_id is null or m.code_id is not null) and (m.status in ('9', '4')) then  ");
		sb.append("nvl((select sum(act_amt) from m_sum_subfund where m.tran_id=tran_id AND (CANCEL_PAYMENT IS NULL OR CANCEL_PAYMENT = 'N')),0) ");
		sb.append("   else 0 ");
		sb.append("end as compensation, ");
		sb.append("case when fn_rate(tran_id)<>'-' then to_char(fn_rate(tran_id)*100) ");
		sb.append(" else '-'  end as salarypercent, ");
		sb.append("FN_SUMS_SALARY(tran_id) as salaryamount, fn_sums_charge_rule(tran_id) as chargeRule, ");
		sb.append("case when m.filename_send like '%ORF%' then 'Y' else '' end as orf, ");
		sb.append("case when m.filename_send like '%APPEAL%' then 'A' ");
		sb.append(" else ");
		sb.append("   case when m.code_id is null and (m.status in ('9', '4')) then 'P' ");
		sb.append("        when m.code_id='DENY' then 'ND' ");
		sb.append("		   when m.code_id is not null and (m.status in ('9', '4')) then 'P' ");
		sb.append("   else 'N' ");
		sb.append("end end as status, ");
		sb.append("to_number(replace(fn_sums_oprefer_hmain(m.tran_id), ',', '')) as orfHMain, ");
		sb.append("to_number(replace(fn_sums_oprefer_nhso(m.tran_id), ',', '')) as orfNHSO, ");
		sb.append("a.hospital_id as hospitalCode, fn_get_hosp_name(a.hospital_id) as hospitalName, ");
		sb.append("m.code_id as codeId, m.code_dw_hc as codeDWHC, m.code_dw_inst as codeDWINST, m.code_dw_ae as codeDWAE, ");
		sb.append("m.code_dw_ip as codeDWIP, m.code_dw_dmis as codeDWDMIS, m.code_dw_ip_cslg as codeDWIPCSLG, ");
		sb.append("m.code_dw_op_cslg as codeDWOPCSLG, m.code_dw_pa_cslg as codeDWPACSLG, m.code_dw_inst_cslg as codeDWINSTCSLG, ");
		sb.append("m.code_dw_ot_cslg as codeDWOTCSLG, ");
		sb.append("case when m.code_id is null and m.code_dw_hc='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPHC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'OPHC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as sumsHC, ");
		sb.append("case when m.code_id is null and m.code_dw_ae='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'OPAE','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPNB','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPPUC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IP3SSS','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IP7SSS','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CARAE','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CARREF','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CARREF-PUC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as sumsAE, ");
		sb.append("case when m.code_id is null and m.code_dw_inst='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'OPINST','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'INST','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as sumsINST, ");
		sb.append("case when m.code_id is null and m.code_dw_ip='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPAEC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPAER','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPINRGC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPINRGR','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPINSPSN','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPPRCC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'IPPRCC-PUC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as sumsIP, ");
		sb.append("case when m.code_id is null and m.code_dw_dmis='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CATARACT','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'CATINST','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'DMISRC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_EXPENSE_AMT_KIDNEY(TRAN_ID,'DMISRC')), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'RCUHOSC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_EXPENSE_AMT_KIDNEY(TRAN_ID,'RCUHOSC')), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'RCUHOSR','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_EXPENSE_AMT_KIDNEY(TRAN_ID,'RCUHOSR')), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LLOP','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LLRGC','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LLRGR','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'LP','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'STROKE-DRUG','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'DMIDML','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0))+ ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND(TRAN_ID,'FPNHSO','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) ");
		sb.append(" else null end as sumsDMIS, ");
		sb.append("to_number(nvl(is_number(FN_SUMS_SUBFUND(m.TRAN_ID,'DRUG','',m.demand_claim_ipd,m.person_type,m.rgn1_af,m.rgn2_af)), 0)) as sumsDRUG, ");
		sb.append("case when m.code_id is null and m.code_dw_ip_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'IP')), 0)) ");
		sb.append(" else null end as sumsIPCS, ");
		sb.append("case when m.code_id is null and m.code_dw_op_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'OP')), 0)) ");
		sb.append(" else null end as sumsOPCS, ");
		sb.append("case when m.code_id is null and m.code_dw_pa_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'PA')), 0)) ");
		sb.append(" else null end as sumsPACS, ");
		sb.append("case when m.code_id is null and m.code_dw_inst_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'INST')), 0)) ");
		sb.append(" else null end as sumsINSTCS, ");
		sb.append("case when m.code_id is null and m.code_dw_ot_cslg='C' then ");
		sb.append("  to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(TRAN_ID,'OT')), 0)) ");
		sb.append(" else null end as sumsOTCS, ");
		sb.append("to_number(nvl(is_number(FN_SUMS_SUBFUND_CS(m.tran_id,'DRUG')), 0)) as sumsDRUGCS ");
		sb.append(" from  m_register m join m_upload a ");
		sb.append(" on m.filename_send=a.filename ");
		sb.append(" and m.hcode=a.hospital_id ");
		sb.append("where ");
		sb.append("a.hospital_id='").append(hospitalCode).append("' ");
		sb.append("and a.hosp_rep_id is not null ");
		if(fromDate != null){
			sb.append("and m.date_send>=to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		}
		if(toDate != null){
			sb.append("and m.date_send<=to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		}
		if(authType != null && authType.length() > 0){
			if(authType.equalsIgnoreCase("UC")){
				sb.append("and (m.filename_send not like '%CS%' and m.filename_send not like '%LG%' and m.filename_send not like '%SS%') ");
			}else if(authType.equalsIgnoreCase("LGO")){
				sb.append("and m.filename_send like '%LG%' ");
			}
		}
		
		sb.append(") sss left join m_register m on m.tran_id=sss.parentTranId ");
		sb.append("where sss.id>").append(start).append(" and sss.id<=").append(end);
		
		this.log.info("get details data from repno with sql query : " + sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		sql.addEntity(SummaryRepData.class);
		final List<SummaryRepData> results = sql.list();
		session.close();
		this.log.info("end get details data from repno..........");

		return results;
	}

	public BigDecimal getSumsTotal(final String hospitalCode, final Date fromDate, final Date toDate, final String authType)throws Exception {
		this.log.info("get sums act amount details data from hospital code : " + hospitalCode);
		final StringBuilder sb = new StringBuilder();
		
		sb.append("select sum(to_number(replace(m.sums_serviceitem, ',', ''))) as sumsTotal ");
		sb.append("from  m_register m join m_upload a ");
		sb.append("on m.filename_send=a.filename and m.hcode=a.hospital_id ");
		sb.append("WHERE a.hospital_id='").append(hospitalCode).append("' ");
		sb.append("and a.hosp_rep_id is not null ");
		if(fromDate != null){
			sb.append("and m.date_send>=to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		}
		if(toDate != null){
			sb.append("and m.date_send<=to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		}
		if(authType != null && authType.length() > 0){
			if(authType.equalsIgnoreCase("UC")){
				sb.append("and (m.filename_send not like '%CS%' and m.filename_send not like '%LG%' and m.filename_send not like '%SS%') ");
			}else if(authType.equalsIgnoreCase("LGO")){
				sb.append("and m.filename_send like '%LG%' ");
			}
		}
		
		this.log.info("get details data from repno with sql query : " + sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		final BigDecimal result = (BigDecimal) sql.uniqueResult();
		session.close();
		this.log.info("end get details data from repno..........");

		return result;
	}

	public BigDecimal getSumsActAmount(final String hospitalCode, final Date fromDate, final Date toDate, final String authType)throws Exception {
		this.log.info("get sums act amount details data from hospital code : " + hospitalCode);
		final StringBuilder sb = new StringBuilder();
		
		sb.append("select sum(nvl((select sum(act_amt) from m_sum_subfund where m.tran_id=tran_id ");
		sb.append("AND (CANCEL_PAYMENT IS NULL OR CANCEL_PAYMENT = 'N')),0)) ");
		sb.append("from  m_register m join m_upload a ");
		sb.append("on m.filename_send=a.filename and m.hcode=a.hospital_id ");
		sb.append("WHERE a.hospital_id='").append(hospitalCode).append("' ");
		sb.append("and a.hosp_rep_id is not null ");
		if(fromDate != null){
			sb.append("and m.date_send>=to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		}
		if(toDate != null){
			sb.append("and m.date_send<=to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		}
		if(authType != null && authType.length() > 0){
			if(authType.equalsIgnoreCase("UC")){
				sb.append("and (m.filename_send not like '%CS%' and m.filename_send not like '%LG%' and m.filename_send not like '%SS%') ");
			}else if(authType.equalsIgnoreCase("LGO")){
				sb.append("and m.filename_send like '%LG%' ");
			}
		}
		
		this.log.info("get details data from repno with sql query : " + sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		final BigDecimal result = (BigDecimal) sql.uniqueResult();
		session.close();
		this.log.info("end get details data from repno..........");

		return result;
	}
	
	public BigDecimal getCountPerson(final String hospitalCode, final Date fromDate, final Date toDate, final ESearchByStatus status, final String authType)throws Exception {
		this.log.info("get sums act amount details data from hospital code : " + hospitalCode);
		final StringBuilder sb = new StringBuilder();

		sb.append("select count(*) ");
		sb.append("from  m_register m join m_upload a ");
		sb.append("on m.filename_send=a.filename and m.hcode=a.hospital_id ");
		sb.append("WHERE a.hospital_id='").append(hospitalCode).append("' ");
		sb.append("and a.hosp_rep_id is not null ");
		if(fromDate != null){
			sb.append("and m.date_send>=to_date('").append(DateFormatUtils.dateToStr(fromDate)).append("', 'dd/MM/yyyy') ");
		}
		if(toDate != null){
			sb.append("and m.date_send<=to_date('").append(DateFormatUtils.dateToStr(toDate)).append("', 'dd/MM/yyyy') ");
		}
		if(ESearchByStatus.APPEAL == status){
			sb.append("and m.filename_send like '%APPEAL%' ");
		}else if(ESearchByStatus.NOT_PASS == status){
			sb.append("and m.code_id<>'DENY' and m.code_id is not null and m.status not in('9', '4') ");
		}else if(ESearchByStatus.NOT_PASS_C == status){
			sb.append("and m.code_id='DENY' ");
		}else if(ESearchByStatus.PASS == status){
			sb.append("and (m.code_id is null ");
			sb.append("or m.code_id is not null) and m.status in ('9', '4') ");
			sb.append("and m.filename_send not like '%APPEAL%' ");
		}
		if(authType != null && authType.length() > 0){
			if(authType.equalsIgnoreCase("UC")){
				sb.append("and (m.filename_send not like '%CS%' and m.filename_send not like '%LG%' and m.filename_send not like '%SS%') ");
			}else if(authType.equalsIgnoreCase("LGO")){
				sb.append("and m.filename_send like '%LG%' ");
			}
		}
		
		this.log.info("get details data from repno with sql query : " + sb.toString());

		final Session session = super.sessionFactory.openSession();
		final SQLQuery sql = session.createSQLQuery(sb.toString());
		final BigDecimal result = (BigDecimal) sql.uniqueResult();
		session.close();
		this.log.info("end get details data from repno..........");

		return result;
	}
	
}
